﻿<cfsilent>
	<cfscript>
		
		sql = "SELECT a.sbj_id,
					   a.sbj_name 
				FROM t_subject a 
				WHERE a.sbj_id = :subjectId ";
		queryObj = new Query( datasource=application.dnsSlave ); 
		queryObj.addParam( name="subjectId", value=event.getArg("SubID"), cfsqltype="cf_sql_varchar" );
		rs_subject= queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT  a.teacher_in_charge,
					   a.assistant,
					   a.cls_name,
					   a.cls_size,
					   a.cls_scale,
					   a.grade,
					   a.cls_found,
					   b.campus_name,
					   c.sbj_name
				FROM t_class a 
					 INNER JOIN t_campus b ON b.campus_id = a.campus_id 
					 INNER JOIN t_subject c ON c.sbj_id = a.sbj_id 
				WHERE a.sbj_id = :Subject 
				ORDER BY a.grade DESC ,a.cls_name ASC ";
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="Subject", value=rs_subject.sbj_id, cfsqltype="cf_sql_varchar" );
		rs_class = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_subject.sbj_name & "行政班信息", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("行政班", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "班级,计划人数,实际人数,年级,建班年月,班主任,班长,辅导员,所在校区,所属专业");
		
		row = 1;
		
		for ( b=1; b LTE rs_class.recordCount; b++) {
			
			row++;
			
			TeacherInCharge = "";
			Monitor = "";
			Assistant = "";
			
			/* 查询班主任 */
			if ( rs_class["teacher_in_charge"][b] neq "" ) {
				
				
				sql = "SELECT a.tch_id, a.tch_name 
						FROM t_teacher a  
						WHERE a.tch_id = :tch_id ";
				queryObj = new Query( datasource=application.dnsSlave ) ;
				queryObj.addParam( name="tch_id", value=rs_class["teacher_in_charge"][b], cfsqltype="cf_sql_varchar" );
				rs_teacher = queryObj.execute( sql=sql ).getResult();
				
				if ( rs_teacher.recordCount ) {
					TeacherInCharge = rs_teacher["tch_name"][1] & "(" & rs_teacher["tch_id"][1] & ")";
				}
			}
			
			/* 查询班长 */
			
			/* 查询辅导员 */
			if ( rs_class["assistant"][b] neq "" ) {
				
				
				sql = "SELECT a.tch_id, a.tch_name 
						FROM t_teacher a  
						WHERE a.tch_id = :tch_id ";
				queryObj = new Query( datasource=application.dnsSlave ) ;
				queryObj.addParam( name="tch_id", value=rs_class["assistant"][b], cfsqltype="cf_sql_varchar" );
				rs_teacherAs = queryObj.execute( sql=sql ).getResult();
					
				if ( rs_teacherAs.recordCount ) {
					Assistant = rs_teacherAs["tch_name"][1] & "(" & rs_teacherAs["tch_id"][1] & ")";
				}
			}
			
			/* 构建班级信息 */
			rowData = rs_class["cls_name"][b]
						& ","
						& rs_class["cls_size"][b]
						& ","
						& rs_class["cls_scale"][b]
						& ","
						& rs_class["grade"][b]
						& ","
						& rs_class["cls_found"][b]
						& ","
						& TeacherInCharge
						& ","
						& Monitor
						& ","
						& Assistant
						& ","
						& rs_class["campus_name"][b]
						& ","
						& rs_class["sbj_name"][b];
			
			/* 写入班级信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>